package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Source;

public class SourceDao {
	private int pageSize=5;
	
	/**
	 * 添加来源
	 */
	public boolean addSource(String name) throws SQLException {
		String sql="insert into source (source_name) values ( ? )";
		return Jdbc.getInstace().executeUpdate(sql, name);
	}
	
	/**
	 * 根据ID删除来源
	 */
	public boolean deleteSourceById(String id) throws SQLException {
		String sql="delete from source where source_id=?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}
	
	/**
	 * 搜索总页数
	 */
	public int getTotalPage( String positionName) throws SQLException {
		String sql = "select count(*) total from source ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, positionName);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	/**
	 * 带条件分页查询
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<Source> getPagepositionListByType(int page, String sourceName)
			throws SQLException {
		int start = (page - 1) * pageSize;
		//String sql = "select * from source where source_id = ? ";
		String sql = "select * from source ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, sourceName);
		sql += " order by source_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}
	/**
	 * 获取所有来源
	 * @throws SQLException 
	 */
	public List<Source> getAllSource() throws SQLException {
		String sql="select * from source";
		List<Map<String,Object>>list=Jdbc.getInstace().executeQueryForList(sql);
		return changelist(list);
	}
	private List<Source> changelist(List<Map<String,Object>>list){
		List<Source>listSource=null;
		if(list!=null&&list.size()>0){
			listSource=new ArrayList<>();
			for (Map<String, Object> map : list) {
				listSource.add(new Source(
						Integer.parseInt(map.get("source_id").toString()),
						(String)map.get("source_name")
						));
			}
		}
		return listSource;
	}
	
	private String buildSqlAndParams(List<Object> params, String sql,String sourceName) {
		//params.add(status);
		if (!StringUtils.isNullOrEmpty(sourceName)) {
			sql += " where source_name like ? ";
			params.add("%" + sourceName + "%");
		}

		return sql;
	}
	
	private List<Source> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Source> sourceList = null;
		if (listData != null && listData.size() > 0) {
			sourceList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				sourceList.add(buildPosition(data));
			}
		}
		return sourceList;
	}
	
	private Source buildPosition(Map<String, Object> data) {
		Source source = new Source();
		source.setSourceName((String)data.get("source_name"));
		source.setSourceId(Integer.parseInt(data.get("source_id").toString()) );
		return source;
	}
}
